Перейти к основному содержимому

3.08. Справочник по Oracle DB

В РАЗРАБОТКЕНЕ ДЛЯ НОВИЧКОВНЕ ОБЯЗАТЕЛЬНО

Разработчику Аналитику Тестировщику
Архитектору Инженеру

Справочник по Oracle DB​

1. Архитектура Oracle Database​

1.1. Основные компоненты экземпляра​

Экземпляр Oracle состоит из двух основных компонентов:

  • Память (System Global Area, SGA)
  • Процессы (Background и Server Processes)

System Global Area (SGA)​

SGA — это совместно используемая память, выделяемая при запуске экземпляра. Она включает:

  • Database Buffer Cache — кэш блоков данных, считанных из файлов данных.
  • Redo Log Buffer — буфер для хранения записей redo до их записи в online redo log файлы.
  • Shared Pool — содержит:
    • Library Cache (SQL-запросы, PL/SQL-блоки, курсоры)
    • Data Dictionary Cache (метаданные о структуре БД)
  • Large Pool — используется для операций резервного копирования, RMAN, параллельного выполнения запросов.
  • Java Pool — память для JVM в базе (при использовании Java-процедур).
  • Streams Pool — используется Oracle Streams (устаревшая технология).
  • Result Cache — кэш результатов SQL-запросов и PL/SQL-функций.

Процессы​

Фоновые процессы (Background Processes):

  • PMON (Process Monitor) — очищает ресурсы после аварийного завершения пользовательских процессов.
  • SMON (System Monitor) — выполняет восстановление экземпляра, коалесценцию свободного пространства.
  • DBWn (Database Writer) — записывает изменённые блоки из buffer cache в файлы данных.
  • LGWR (Log Writer) — записывает содержимое redo log buffer в online redo log файлы.
  • CKPT (Checkpoint) — сигнализирует DBWn о необходимости записи и обновляет контрольные точки в файлах управления и данных.
  • ARCn (Archiver) — архивирует online redo log файлы при включённом режиме ARCHIVELOG.
  • MMON, MMNL — мониторинг и сбор статистики AWR.
  • LREG — регистрирует службу в listener (начиная с 12c).

Серверные процессы (Server Processes): Обрабатывают запросы пользователей, читают данные из диска в SGA, выполняют SQL.

2. Физическая структура базы данных​

2.1. Файлы данных (Data Files)​

  • Хранят данные таблиц, индексов, LOB-объектов.
  • Принадлежат одному табличному пространству (tablespace).
  • Имеют расширение .dbf или .ora.

2.2. Файлы управления (Control Files)​

  • Содержат метаданные о структуре БД: имена файлов данных, redo log файлов, состояние БД, SCN.
  • Обычно дублируются для отказоустойчивости.
  • Расширение .ctl.

2.3. Online Redo Log Files​

  • Циклические файлы, содержащие изменения, внесённые в БД.
  • Группы redo log файлов (обычно минимум две группы, по два файла в каждой).
  • При переключении группы (log switch) происходит checkpoint.

2.4. Archive Log Files​

  • Копии online redo log файлов, сохраняемые при включённом режиме ARCHIVELOG.
  • Используются для восстановления на момент времени (point-in-time recovery).

2.5. Password File​

  • Хранит учётные данные пользователей с привилегиями SYSDBA, SYSOPER.
  • Необходим для удалённого администрирования.

2.6. Parameter File (PFILE / SPFILE)​

  • PFILE — текстовый файл (init<SID>.ora), редактируется вручную.
  • SPFILE — двоичный файл (spfile<SID>.ora), управляется через SQL.
  • Содержит параметры запуска экземпляра.

3. Параметры инициализации (Initialization Parameters)​

Oracle поддерживает более 300 параметров. Ниже — ключевые категории и часто используемые параметры.

3.1. Память​

ПараметрОписание
MEMORY_TARGETАвтоматическое управление SGA + PGA (AMM).
MEMORY_MAX_TARGETМаксимальный объём памяти для AMM.
SGA_TARGETАвтоматическое управление компонентами SGA (ASMM).
SGA_MAX_SIZEМаксимальный размер SGA.
PGA_AGGREGATE_TARGETЦелевой объём PGA для всех сессий.

3.2. Файлы и пути​

ПараметрОписание
DB_NAMEИмя базы данных (до 8 символов).
DB_UNIQUE_NAMEУникальное имя в Data Guard конфигурации.
CONTROL_FILESСписок путей к control files.
DB_CREATE_FILE_DESTПуть по умолчанию для автоматического создания файлов (OMF).
DB_RECOVERY_FILE_DESTКаталог для архивных логов, резервных копий, flashback logs.
DB_RECOVERY_FILE_DEST_SIZEМаксимальный размер FRA (Fast Recovery Area).

3.3. Режимы работы​

ПараметрОписание
ARCHIVELOG / NOARCHIVELOGУстанавливается командой, не параметром.
COMPATIBLEМинимальная версия Oracle, с которой совместима БД.
OPEN_CURSORSМаксимальное число открытых курсоров на сессию.
PROCESSESМаксимальное число процессов (включая фоновые).
SESSIONSМаксимальное число сессий (автоматически ≈ 1.1 × PROCESSES + 5).

3.4. Безопасность​

ПараметрОписание
REMOTE_LOGIN_PASSWORDFILEEXCLUSIVE, SHARED, NONE — управление password file.
AUDIT_TRAILDB, OS, XML — включение аудита.
SEC_CASE_SENSITIVE_LOGONЧувствительность к регистру при входе.

3.5. Оптимизатор​

ПараметрОписание
OPTIMIZER_MODEALL_ROWS, FIRST_ROWS_n, CHOOSE (устарело).
OPTIMIZER_FEATURES_ENABLEВерсия оптимизатора (например, '19.1.0').
CURSOR_SHARINGEXACT, FORCE, SIMILAR — управление общими курсорами.

4. Типы данных Oracle​

4.1. Числовые​

  • NUMBER(p, s) — произвольная точность.
  • INTEGER, SMALLINT, DECIMAL — псевдонимы NUMBER.
  • BINARY_FLOAT, BINARY_DOUBLE — IEEE 754 числа с плавающей точкой.

4.2. Символьные​

  • CHAR(n) — фиксированная длина (до 2000 байт).
  • VARCHAR2(n) — переменная длина (до 4000 байт; 32767 при MAX_STRING_SIZE=EXTENDED).
  • NCHAR, NVARCHAR2 — Unicode (AL16UTF16 или UTF8).
  • CLOB, NCLOB — большие текстовые объекты (до 128 TB).

4.3. Дата и время​

  • DATE — дата и время (точность до секунды).
  • TIMESTAMP — с микросекундами.
  • TIMESTAMP WITH TIME ZONE
  • TIMESTAMP WITH LOCAL TIME ZONE
  • INTERVAL YEAR TO MONTH
  • INTERVAL DAY TO SECOND

4.4. Бинарные​

  • RAW(n) — до 2000 байт.
  • LONG RAW — устаревший (до 2 GB).
  • BLOB — двоичные данные (до 128 TB).
  • BFILE — указатель на внешний файл.

4.5. Прочие​

  • ROWID — уникальный адрес строки.
  • UROWID — универсальный ROWID (для индексов IOT).
  • XMLType — хранение XML-документов.
  • JSON — начиная с 12c, поддержка JSON через VARCHAR2/CLOB/BLOB с проверкой.

5. Встроенные SQL-функции​

5.1. Числовые​

  • ABS, CEIL, FLOOR, ROUND, TRUNC
  • MOD, POWER, SQRT, EXP, LN, LOG
  • SIGN, BITAND

5.2. Строковые​

  • UPPER, LOWER, INITCAP
  • SUBSTR, INSTR, LENGTH, LPAD, RPAD
  • TRIM, LTRIM, RTRIM
  • REPLACE, TRANSLATE
  • CONCAT (или ||)
  • REGEXP_SUBSTR, REGEXP_REPLACE, REGEXP_LIKE, REGEXP_INSTR

5.3. Дата/время​

  • SYSDATE, SYSTIMESTAMP, CURRENT_DATE
  • ADD_MONTHS, MONTHS_BETWEEN
  • NEXT_DAY, LAST_DAY
  • EXTRACT(YEAR FROM ...), EXTRACT(MONTH FROM ...)
  • NUMTODSINTERVAL, NUMTOYMINTERVAL

5.4. Преобразования​

  • TO_CHAR, TO_DATE, TO_TIMESTAMP
  • TO_NUMBER
  • CAST(...) AS ...
  • NVL, NVL2, COALESCE
  • DECODE, CASE

5.5. Агрегатные​

  • SUM, AVG, MIN, MAX, COUNT
  • STDDEV, VARIANCE
  • LISTAGG — конкатенация строк
  • MEDIAN, PERCENTILE_CONT, PERCENTILE_DISC

5.6. Аналитические​

  • ROW_NUMBER(), RANK(), DENSE_RANK()
  • LEAD, LAG
  • FIRST_VALUE, LAST_VALUE
  • NTILE
  • OVER (PARTITION BY ... ORDER BY ...)

6. Системные представления (Data Dictionary Views)​

6.1. Общие префиксы​

  • USER_* — объекты текущего пользователя.
  • ALL_* — объекты, доступные текущему пользователю.
  • DBA_* — все объекты в БД (требуют привилегий).
  • V$* — динамические представления производительности (на основе X$ таблиц).

6.2. Часто используемые представления​

ПредставлениеНазначение
DBA_TABLESВсе таблицы в БД.
DBA_INDEXESВсе индексы.
DBA_USERSПользователи.
DBA_ROLESРоли.
DBA_SYS_PRIVSСистемные привилегии.
DBA_TAB_PRIVSПривилегии на объекты.
V$SESSIONАктивные сессии.
V$PROCESSПроцессы ОС.
V$SQLКэш SQL-запросов.
V$LOCK, V$SESSION_WAITБлокировки и ожидания.
V$PARAMETERТекущие параметры.
V$DATAFILE, V$CONTROLFILE, V$LOGФизические файлы.
DBA_SEGMENTSСегменты (таблицы, индексы и т.д.).
DBA_EXTENTSЭкстенты сегментов.
DBA_FREE_SPACEСвободное место в табличных пространствах.

7. Управление пользователями и привилегиями​

7.1. Создание пользователя​

CREATE USER username IDENTIFIED BY password
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA 100M ON users;

7.2. Привилегии​

Системные:

  • CREATE SESSION
  • CREATE TABLE
  • CREATE VIEW
  • CREATE PROCEDURE
  • UNLIMITED TABLESPACE

Объектные:

  • SELECT, INSERT, UPDATE, DELETE на таблицу
  • EXECUTE на процедуру
  • REFERENCES на внешний ключ

Роли:

  • CONNECT, RESOURCE, DBA (устаревшие, но используются)
  • PDB_DBA (в multitenant)

7.3. Аудит​

AUDIT SELECT ON schema.table;
AUDIT CREATE SESSION WHENEVER SUCCESSFUL;
NOAUDIT ...;

8. Табличные пространства и сегменты​

8.1. Типы табличных пространств​

  • Permanent — для постоянных данных.
  • Temporary — для временных сегментов (сортировки, хэши).
  • Undo — для хранения undo-информации (автоматически управляется).

8.2. Создание​

CREATE TABLESPACE tbs1 DATAFILE '/u01/oradata/orcl/tbs01.dbf' SIZE 100M;
CREATE TEMPORARY TABLESPACE temp_tbs TEMPFILE '/u01/oradata/orcl/temp01.dbf' SIZE 50M;

8.3. Управление квотами​

ALTER USER scott QUOTA UNLIMITED ON users;
ALTER USER scott QUOTA 50M ON tbs1;

9. Резервное копирование и восстановление​

9.1. Режимы​

  • NOARCHIVELOG — только полное восстановление до последнего backup.
  • ARCHIVELOG — восстановление до любого момента времени.

9.2. RMAN (Recovery Manager)​

Основные команды:

RMAN TARGET /

BACKUP DATABASE;
BACKUP ARCHIVELOG ALL;
BACKUP CURRENT CONTROLFILE;

RESTORE DATABASE;
RECOVER DATABASE;

LIST BACKUP;
REPORT OBSOLETE;
DELETE OBSOLETE;

9.3. Flashback​

  • FLASHBACK DATABASE — откат всей БД.
  • FLASHBACK TABLE — откат таблицы.
  • FLASHBACK QUERY — запрос к состоянию таблицы в прошлом: 
    SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);

10. PL/SQL — Процедурное расширение SQL​

10.1. Базовая структура блока​

DECLARE
  -- объявления переменных, курсоров, типов
BEGIN
  -- исполняемая часть
EXCEPTION
  -- обработка ошибок
END;

Анонимные блоки не сохраняются в БД. Именованные объекты (процедуры, функции, пакеты) хранятся в словаре данных.

10.2. Типы данных в PL/SQL​

  • Скалярные: NUMBER, VARCHAR2, DATE, BOOLEAN
  • Составные: RECORD, TABLE (ассоциативные массивы, nested tables, VARRAY)
  • Ссылочные: REF CURSOR
  • LOB: CLOB, BLOB

10.3. Переменные и константы​

v_name VARCHAR2(100) := 'John';
c_pi CONSTANT NUMBER := 3.14159;

10.4. Условные конструкции​

IF condition THEN
  ...
ELSIF condition2 THEN
  ...
ELSE
  ...
END IF;

10.5. Циклы​

-- Простой цикл
LOOP
  EXIT WHEN condition;
END LOOP;

-- Цикл с условием
WHILE condition LOOP
  ...
END LOOP;

-- Цикл по диапазону
FOR i IN 1..10 LOOP
  ...
END LOOP;

10.6. Курсоры​

Неявный курсор — используется автоматически при выполнении DML.

Явный курсор:

DECLARE
  CURSOR emp_cur IS
    SELECT employee_id, last_name FROM employees WHERE department_id = 10;
  v_emp emp_cur%ROWTYPE;
BEGIN
  OPEN emp_cur;
  LOOP
    FETCH emp_cur INTO v_emp;
    EXIT WHEN emp_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp.last_name);
  END LOOP;
  CLOSE emp_cur;
END;

Курсор с параметрами:

CURSOR emp_cur(dept_id NUMBER) IS
  SELECT * FROM employees WHERE department_id = dept_id;

Курсор FOR-LOOP (автоматическое открытие/закрытие):

FOR rec IN (SELECT * FROM employees WHERE salary > 5000) LOOP
  DBMS_OUTPUT.PUT_LINE(rec.last_name);
END LOOP;

10.7. Обработка исключений​

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    ...
  WHEN TOO_MANY_ROWS THEN
    ...
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);

Предопределённые исключения:

  • NO_DATA_FOUND
  • TOO_MANY_ROWS
  • DUP_VAL_ON_INDEX
  • INVALID_NUMBER
  • ZERO_DIVIDE
  • PROGRAM_ERROR

Пользовательские исключения:

DECLARE
  e_custom EXCEPTION;
BEGIN
  RAISE e_custom;
EXCEPTION
  WHEN e_custom THEN
    ...
END;

Использование PRAGMA EXCEPTION_INIT для привязки к ORA-кодам:

PRAGMA EXCEPTION_INIT(e_timeout, -3135);

10.8. Хранимые процедуры и функции​

Процедура:

CREATE OR REPLACE PROCEDURE raise_salary (
  p_emp_id IN NUMBER,
  p_amount IN NUMBER DEFAULT 100
) AS
BEGIN
  UPDATE employees SET salary = salary + p_amount
  WHERE employee_id = p_emp_id;
  COMMIT;
END;

Функция:

CREATE OR REPLACE FUNCTION get_dept_name(p_dept_id NUMBER) RETURN VARCHAR2 IS
  v_name departments.department_name%TYPE;
BEGIN
  SELECT department_name INTO v_name
  FROM departments WHERE department_id = p_dept_id;
  RETURN v_name;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RETURN NULL;
END;

Вызов:

EXEC raise_salary(101, 200);
SELECT get_dept_name(10) FROM dual;

10.9. Пакеты (Packages)​

Пакет состоит из спецификации (интерфейс) и тела (реализация).

Спецификация:

CREATE OR REPLACE PACKAGE emp_pkg AS
  PROCEDURE hire_employee(p_name VARCHAR2, p_salary NUMBER);
  FUNCTION get_salary(p_id NUMBER) RETURN NUMBER;
  g_counter NUMBER := 0; -- глобальная переменная
END emp_pkg;

Тело:

CREATE OR REPLACE PACKAGE BODY emp_pkg AS
  PROCEDURE hire_employee(p_name VARCHAR2, p_salary NUMBER) IS
  BEGIN
    INSERT INTO employees (employee_id, last_name, salary)
    VALUES (emp_seq.NEXTVAL, p_name, p_salary);
    g_counter := g_counter + 1;
  END;

  FUNCTION get_salary(p_id NUMBER) RETURN NUMBER IS
    v_sal NUMBER;
  BEGIN
    SELECT salary INTO v_sal FROM employees WHERE employee_id = p_id;
    RETURN v_sal;
  END;
END emp_pkg;

Пакеты поддерживают перегрузку процедур/функций и инициализацию через блок BEGIN ... END; в теле.

11. Триггеры​

11.1. Типы триггеров​

  • DML-триггеры: BEFORE/AFTER на INSERT, UPDATE, DELETE
  • INSTEAD OF — для представлений
  • DDL-триггеры: на CREATE, DROP, ALTER
  • Системные триггеры: LOGON, LOGOFF, STARTUP, SHUTDOWN

11.2. Пример DML-триггера​

CREATE OR REPLACE TRIGGER trg_emp_audit
  AFTER UPDATE ON employees
  FOR EACH ROW
DECLARE
  v_user VARCHAR2(30) := USER;
BEGIN
  INSERT INTO emp_audit (emp_id, old_sal, new_sal, changed_by, change_date)
  VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, v_user, SYSDATE);
END;

:OLD и :NEW — псевдозаписи для значений до и после изменения.

11.3. Мутационные таблицы​

Обновление таблицы внутри триггера на ту же таблицу вызывает ошибку ORA-04091. Решение — использование составного триггера (COMPOUND TRIGGER) или временных таблиц.

12. Индексы​

12.1. Типы индексов​

  • B-tree — стандартный индекс.
  • Bitmap — для колонок с низкой кардинальностью (пол, статус).
  • Function-based — индекс по выражению: 
    CREATE INDEX idx_upper_name ON employees(UPPER(last_name));
  • Reverse key — для уменьшения горячих блоков при последовательных вставках.
  • Domain (Text) — для полнотекстового поиска (CTXSYS.CONTEXT).
  • Spatial, XMLIndex, JSON Search Index — специализированные.

12.2. Управление индексами​

ALTER INDEX idx_name REBUILD;
ALTER INDEX idx_name MONITORING USAGE;
DROP INDEX idx_name;

Индексы автоматически неактивны при UNUSABLE состоянии (например, после MOVE таблицы).

13. Партиционирование​

13.1. Типы партиционирования​

  • Range — по диапазону (дата, число)
  • List — по списку значений
  • Hash — равномерное распределение
  • Composite — комбинация (например, range-hash)

13.2. Пример​

CREATE TABLE sales (
  sale_id NUMBER,
  sale_date DATE,
  amount NUMBER
)
PARTITION BY RANGE (sale_date) (
  PARTITION p_2024 VALUES LESS THAN (DATE '2025-01-01'),
  PARTITION p_2025 VALUES LESS THAN (DATE '2026-01-01')
);

13.3. Преимущества​

  • Ускорение запросов (partition pruning)
  • Упрощение обслуживания (удаление целой партиции)
  • Улучшение параллелизма

14. Производительность и мониторинг​

14.1. AWR (Automatic Workload Repository)​

Сбор статистики каждые часы (по умолчанию). Используется для анализа производительности.

-- Создать отчёт
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

14.2. ADDM (Automatic Database Diagnostic Monitor)​

Анализирует AWR и даёт рекомендации.

14.3. SQL Tuning Advisor​

DECLARE
  task_name VARCHAR2(30);
BEGIN
  task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_text => 'SELECT * FROM employees WHERE department_id = 10',
    time_limit => 60
  );
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name);
END;

14.4. Ожидания (Waits)​

Ключевые события:

  • db file sequential read — индексные чтения
  • db file scattered read — full table scan
  • enq: TX - row lock contention — блокировки строк
  • log file sync — медленный COMMIT

14.5. План выполнения​

EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Или в реальном времени:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

15. Безопасность​

15.1. Привилегии​

  • Системные: CREATE SESSION, CREATE TABLE
  • Объектные: SELECT, EXECUTE
  • Роли: CONNECT, RESOURCE, DBA

15.2. Аудит​

AUDIT SELECT ON hr.employees BY ACCESS;
AUDIT CREATE ANY TABLE BY hr BY SESSION;

Аудит можно включить через AUDIT_TRAIL = DB, EXTENDED.

15.3. Transparent Data Encryption (TDE)​

Шифрование табличных пространств:

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "password";
ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY IDENTIFIED BY "password";

CREATE TABLESPACE secure_tbs DATAFILE ... ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);

16. Multitenant Architecture (начиная с 12c)​

  • CDB (Container Database) — корневой контейнер.
  • PDB (Pluggable Database) — подключаемая БД, изолированная логически.

Управление:

CREATE PLUGGABLE DATABASE pdb1 ADMIN USER pdb1admin IDENTIFIED BY password;
ALTER PLUGGABLE DATABASE pdb1 OPEN;
ALTER SESSION SET CONTAINER = pdb1;

17. Высокая доступность и репликация​

17.1. Oracle Data Guard​

Data Guard обеспечивает защиту от сбоев через поддержание одной или нескольких резервных копий базы данных.

Типы standby-баз:​

  • Physical Standby — точная побайтовая копия, применяет redo-записи.
  • Logical Standby — логическая копия, применяет SQL-транзакции; допускает локальные изменения.
  • Snapshot Standby — временно конвертируется в читаемо-записываемую БД для тестирования.

Режимы защиты:​

  • Maximum Performance — асинхронная передача redo (по умолчанию).
  • Maximum Availability — синхронная передача с автоматическим failover при использовании Fast-Start Failover.
  • Maximum Protection — синхронная передача; первичная БД останавливается при недоступности standby.

Управление через DGMGRL:​

DGMGRL> CONNECT sys/password@primary
DGMGRL> CREATE CONFIGURATION dg_config AS PRIMARY DATABASE IS primary_db CONNECT IDENTIFIER IS primary;
DGMGRL> ADD DATABASE standby_db AS CONNECT IDENTIFIER IS standby;
DGMGRL> ENABLE CONFIGURATION;
DGMGRL> FAILOVER TO standby_db;

Ключевые представления:​

  • V$DATAGUARD_STATS
  • V$ARCHIVED_LOG
  • V$DATAGUARD_PROCESS

17.2. Real Application Clusters (RAC)​

RAC — кластер из нескольких узлов, работающих с одной общей базой данных.

Компоненты:​

  • Shared Storage — ASM (Automatic Storage Management) или сторонние решения.
  • Clusterware — Oracle Cluster Registry (OCR), Voting Disk.
  • SCAN (Single Client Access Name) — единое имя для подключения клиентов.

Администрирование:​

srvctl start database -d orcl
srvctl stop instance -d orcl -i orcl1
crsctl check cluster

Представления:​

  • GV$SESSION — сессии по всем узлам
  • GV$INSTANCE
  • GV$LOCK

18. Планировщик заданий (DBMS_SCHEDULER)​

18.1. Создание задания​

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'nightly_backup',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN backup_proc; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2',
    enabled         => TRUE
  );
END;

18.2. Типы заданий​

  • PLSQL_BLOCK
  • STORED_PROCEDURE
  • EXECUTABLE — запуск ОС-команд (требует credentials)
  • CHAIN — последовательность шагов с условиями перехода

18.3. Управление​

DBMS_SCHEDULER.RUN_JOB('nightly_backup');
DBMS_SCHEDULER.DISABLE('nightly_backup');
DBMS_SCHEDULER.DROP_JOB('nightly_backup');

18.4. Мониторинг​

  • DBA_SCHEDULER_JOBS
  • DBA_SCHEDULER_JOB_RUN_DETAILS
  • DBA_SCHEDULER_JOB_LOG

19. Материализованные представления (Materialized Views)​

Используются для кэширования результатов сложных запросов, особенно в хранилищах данных.

19.1. Создание​

CREATE MATERIALIZED VIEW mv_sales_summary
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
AS
SELECT department_id, SUM(salary) total_sal
FROM employees
GROUP BY department_id;

19.2. Типы обновления​

  • COMPLETE — полная перестройка
  • FAST — инкрементальное обновление (требует materialized view log)
  • FORCE — пытается FAST, иначе COMPLETE

19.3. Materialized View Log​

CREATE MATERIALIZED VIEW LOG ON employees
WITH PRIMARY KEY, ROWID, SEQUENCE (salary, department_id)
INCLUDING NEW VALUES;

19.4. Query Rewrite​

Если включено, оптимизатор может автоматически использовать MV вместо исходных таблиц:

ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;

20. Внешние таблицы (External Tables)​

Позволяют читать данные из файлов ОС как из обычных таблиц.

20.1. Создание​

CREATE DIRECTORY ext_dir AS '/u01/ext_data';

CREATE TABLE ext_employees (
  emp_id NUMBER,
  name VARCHAR2(50),
  salary NUMBER
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
  )
  LOCATION ('employees.csv')
)
REJECT LIMIT UNLIMITED;

20.2. Использование​

Только для чтения. Поддерживает параллельную загрузку.

21. Работа с JSON​

Начиная с Oracle 12c, встроена поддержка JSON.

21.1. Хранение​

CREATE TABLE json_docs (
  id NUMBER,
  doc CLOB CHECK (doc IS JSON)
);

21.2. Запросы​

SELECT j.doc.name FROM json_docs j
WHERE JSON_EXISTS(j.doc, '$.address.city');

SELECT JSON_VALUE(doc, '$.name') FROM json_docs;
SELECT JSON_QUERY(doc, '$.orders') FROM json_docs;

21.3. Индексирование​

CREATE INDEX idx_json_name ON json_docs (JSON_VALUE(doc, '$.name' RETURNING VARCHAR2(50)));
-- Или функциональный индекс:
CREATE INDEX idx_json_func ON json_docs (JSON_VALUE(doc, '$.age' RETURNING NUMBER));

22. Работа с XML​

22.1. Тип XMLType​

CREATE TABLE xml_docs (
  id NUMBER,
  doc XMLType
);

22.2. Запросы​

SELECT EXTRACT(doc, '/book/title') FROM xml_docs;
SELECT EXTRACTVALUE(doc, '/book/author') FROM xml_docs;

-- Использование XQuery
SELECT XMLQuery('//title' PASSING doc RETURNING CONTENT) FROM xml_docs;

22.3. Индексы​

  • XMLIndex — для ускорения XPath-запросов
  • Function-based — на основе EXTRACTVALUE

23. Практические административные сценарии​

23.1. Перемещение таблицы в другое табличное пространство​

ALTER TABLE employees MOVE TABLESPACE new_tbs;
-- Перестроить индексы
ALTER INDEX emp_pk REBUILD;

23.2. Сжатие таблицы​

ALTER TABLE sales MOVE COMPRESS FOR OLTP;
-- Или для партиций
ALTER TABLE sales MOVE PARTITION p_2024 COMPRESS;

23.3. Анализ статистики​

EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');
EXEC DBMS_STATS.GATHER_DATABASE_STATS;

23.4. Поиск блокировок​

SELECT s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking '
       || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
FROM v$lock l1, v$session s1, v$lock l2, v$session s2
WHERE s1.sid = l1.sid AND s2.sid = l2.sid
  AND l1.BLOCK = 1 AND l2.request > 0
  AND l1.id1 = l2.id1 AND l1.id2 = l2.id2;

23.5. Убить сессию​

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

24. Управление ресурсами (Resource Manager)​

Позволяет распределять CPU, параллелизм и другие ресурсы между группами пользователей.

24.1. Создание плана​

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP('OLTP_USERS', 'High priority');
  DBMS_RESOURCE_MANAGER.CREATE_PLAN('DAY_PLAN', 'Daytime plan');
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan => 'DAY_PLAN',
    group_or_subplan => 'OLTP_USERS',
    cpu_p1 => 80
  );
  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;

24.2. Назначение пользователя группе​

EXEC DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
  attribute => 'ORACLE_USER',
  value => 'APP_USER',
  consumer_group => 'OLTP_USERS'
);

24.3. Активация плана​

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'DAY_PLAN';

25. Миграция и обновление​

25.1. Обновление версии Oracle​

Oracle поддерживает два основных подхода:

  • In-place upgrade — обновление существующего экземпляра.
  • Data Pump migration — экспорт/импорт через expdp/impdp.

Этапы in-place upgrade:​

  1. Проверка совместимости:

    SET SERVEROUTPUT ON;
    DECLARE
      ret VARCHAR2(100);
    BEGIN
      ret := DBMS_DB_VERSION.CHECK_COMPONENT('Oracle Server', '19.0.0.0.0');
      DBMS_OUTPUT.PUT_LINE(ret);
    END;

  2. Запуск Pre-Upgrade Information Tool:

    $ORACLE_HOME/jdk/bin/java -jar /u01/preupgrade.jar TERMINAL TEXT

  3. Резервное копирование всей БД.

  4. Выполнение скриптов catctl.pl:

    cd $ORACLE_HOME/rdbms/admin
    $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql

  5. Пост-апгрейд проверки и компиляция недействительных объектов.

25.2. Миграция с других СУБД​

Используется Oracle SQL Developer Migration Workbench:

  • Поддерживает миграцию из MySQL, SQL Server, Sybase, Access.
  • Автоматически конвертирует типы данных, синтаксис, хранимые процедуры.
  • Генерирует отчёт о несовместимостях.

25.3. Data Pump (expdp/impdp)​

Экспорт схемы:

expdp system/password DIRECTORY=dp_dir DUMPFILE=hr.dmp SCHEMAS=hr

Импорт с переносом:

impdp system/password DIRECTORY=dp_dir DUMPFILE=hr.dmp REMAP_SCHEMA=hr:hr_new

Параметры:

  • CONTENT=ALL|DATA_ONLY|METADATA_ONLY
  • EXCLUDE=STATISTICS,INDEX
  • PARALLEL=N

26. Диагностика ошибок (ORA-коды)​

26.1. Часто встречающиеся ошибки​

КодОписаниеРешение
ORA-00001Нарушение уникального ограниченияПроверить данные или временно отключить индекс
ORA-00942Таблица или представление не существуетПроверить имя, привилегии, регистр
ORA-01031Недостаточно привилегийЗапросить GRANT или использовать учётную запись с правами
ORA-01555Snapshot too oldУвеличить UNDO_RETENTION, уменьшить длительность транзакции
ORA-01652Не удаётся расширить temp-сегментУвеличить TEMP tablespace
ORA-04031Не хватает памяти в shared poolУвеличить SHARED_POOL_SIZE или включить ASMM
ORA-12514TNS:listener does not know of serviceПроверить tnsnames.ora, listener.ora, статус listener
ORA-12899Значение слишком велико для столбцаУвеличить размер столбца или обрезать данные

26.2. Инструменты диагностики​

  • ADRCI (Automatic Diagnostic Repository Command Interpreter):

    adrci
    show alert
    show incident

  • Trace-файлы: находятся в $ADR_HOME/trace/, содержат детали ошибок.

  • SQL Trace + TKPROF:

    ALTER SESSION SET SQL_TRACE = TRUE;
    -- выполнить запрос
    EXIT;

    Затем обработать:

    tkprof ora_12345.trc output.txt

27. Проектирование схем базы данных​

27.1. Нормализация​

  • 1NF — атомарные значения
  • 2NF — отсутствие частичных зависимостей
  • 3NF — отсутствие транзитивных зависимостей
  • BCNF — каждая детерминанта — кандидатский ключ

27.2. Денормализация​

Применяется в хранилищах данных для ускорения аналитических запросов.

27.3. Именование​

Рекомендации:

  • Таблицы: employees, order_items
  • Первичные ключи: employee_id
  • Внешние ключи: department_id (ссылающийся на departments.department_id)
  • Индексы: idx_emp_dept, pk_employees
  • Последовательности: emp_seq

27.4. Ограничения​

  • PRIMARY KEY — гарантирует уникальность и NOT NULL
  • FOREIGN KEY — ссылочная целостность
  • CHECK — бизнес-правила (salary > 0)
  • UNIQUE — уникальность без NOT NULL
  • NOT NULL — обязательное заполнение

27.5. Последовательности и идентификаторы​

CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 NOCACHE;
-- Или автоинкремент (12c+):
CREATE TABLE employees (
  id NUMBER GENERATED ALWAYS AS IDENTITY,
  name VARCHAR2(100)
);

28. Комплексные рекомендации по эксплуатации​

28.1. Резервное копирование​

  • Ежедневный RMAN backup уровня 0 (полный) + уровень 1 (инкрементальный).
  • Архивные логи — каждый час.
  • Тестирование восстановления — ежеквартально.
  • Хранение копий вне дата-центра.

28.2. Мониторинг​

  • Настройка Enterprise Manager Cloud Control или OEM Express.
  • Алерты на:
    • Заполнение табличных пространств (>85%)
    • Долгие запросы (>5 сек)
    • Ошибки в alert log
    • Превышение PGA/SGA

28.3. Безопасность​

  • Отключение учётных записей по умолчанию (SCOTT, HR).
  • Регулярная смена паролей.
  • Использование профилей: 
    CREATE PROFILE app_profile LIMIT
      FAILED_LOGIN_ATTEMPTS 3
      PASSWORD_LOCK_TIME 1
      PASSWORD_LIFE_TIME 90;
    ALTER USER app_user PROFILE app_profile;

28.4. Производительность​

  • Избегать SELECT *.
  • Использовать bind-переменные.
  • Регулярно собирать статистику.
  • Анализировать планы выполнения перед продакшеном.
  • Ограничивать параллелизм для OLTP.

28.5. Документирование​

  • Хранить DDL-скрипты в системе контроля версий.
  • Фиксировать все изменения через change management.
  • Поддерживать актуальную ER-диаграмму.